IF OBJECT_ID('dbo.fnGetSponsorAwardNumber') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetSponsorAwardNumber
    IF OBJECT_ID('dbo.fnGetSponsorAwardNumber') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetSponsorAwardNumber >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetSponsorAwardNumber >>>'
END
GO

-- =======================================================================
-- Function Name: dbo.fnGetSponsorAwardNumber()
--
-- Author:      Chris Bernard, Parallax Consulting LLC
--
-- Create date: 10/18/2007
--
-- Description: Removes special charcters from Sponsor Award
--
-- Parameters:  @SponsorAward = VARCHAR(100) 
--              
-- Returns:     VARCHAR(100) 
--
-- SampleCall:
-- SELECT @SponsorAward = dbo.fnGetSponsorAwardNumber ( @SponsorAward )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/18/08     CB        Created
-- =======================================================================
CREATE FUNCTION [dbo].[fnGetSponsorAwardNumber](
     @SponsorAward VARCHAR(100)
)
     RETURNS VARCHAR(100)
AS
BEGIN
	IF @SponsorAward IS NOT NULL
	BEGIN
		SET @SponsorAward = REPLACE(@SponsorAward,'_','')
		SET @SponsorAward = REPLACE(@SponsorAward,'-','')
		SET @SponsorAward = REPLACE(@SponsorAward,'--','')
		SET @SponsorAward = REPLACE(@SponsorAward,'/','')
		SET @SponsorAward = REPLACE(@SponsorAward,'\','')
		SET @SponsorAward = REPLACE(@SponsorAward,' ','')
    END
   

	RETURN @SponsorAward 
END

GO
IF OBJECT_ID('dbo.fnGetSponsorAwardNumber') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetSponsorAwardNumber >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetSponsorAwardNumber >>>'
GO

GRANT EXECUTE ON dbo.fnGetSponsorAwardNumber TO InsightReport 
GO
